-- ============================================================
-- EMAIL SYSTEM ADDITIONAL TABLES
-- ============================================================
-- Additional tables required for the comprehensive email system
-- These extend the base schema.sql with email-specific functionality
-- ============================================================

-- Email tracking events table
CREATE TABLE email_tracking_events (
    event_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    digest_id UUID REFERENCES email_digests(digest_id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL,
    event_data JSONB DEFAULT '{}',
    ip_address INET,
    user_agent TEXT,
    referrer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_event_type CHECK (event_type IN (
        'open', 'click', 'bounce', 'unsubscribe', 'spam_complaint', 'delivered'
    ))
);

-- Email link tracking table
CREATE TABLE email_link_tracking (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    digest_id UUID REFERENCES email_digests(digest_id) ON DELETE CASCADE,
    original_url TEXT NOT NULL,
    link_type VARCHAR(100) DEFAULT 'general',
    click_count INTEGER DEFAULT 0,
    unique_clicks INTEGER DEFAULT 0,
    last_clicked TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Email consent records for GDPR/CAN-SPAM compliance
CREATE TABLE email_consent_records (
    consent_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    consent_type VARCHAR(50) NOT NULL,
    consent_source VARCHAR(50) NOT NULL,
    consent_data JSONB DEFAULT '{}',
    consent_status VARCHAR(50) DEFAULT 'pending',
    confirmation_token VARCHAR(255),
    confirmation_sent_at TIMESTAMP,
    confirmed_at TIMESTAMP,
    withdrawn_at TIMESTAMP,
    withdrawal_method VARCHAR(50),
    withdrawal_metadata JSONB,
    gdpr_compliant BOOLEAN DEFAULT true,
    can_spam_compliant BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_consent_type CHECK (consent_type IN (
        'opt_in', 'double_opt_in', 'legitimate_interest', 'contract'
    )),
    CONSTRAINT chk_consent_source CHECK (consent_source IN (
        'registration', 'subscription_form', 'api', 'import', 'manual'
    )),
    CONSTRAINT chk_consent_status CHECK (consent_status IN (
        'pending', 'confirmed', 'withdrawn', 'expired'
    ))
);

-- Unsubscribe tokens for secure unsubscribe links
CREATE TABLE unsubscribe_tokens (
    token_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    token_type VARCHAR(50) NOT NULL,
    digest_id UUID REFERENCES email_digests(digest_id),
    specific_target VARCHAR(200),
    expires_at TIMESTAMP NOT NULL,
    is_used BOOLEAN DEFAULT false,
    used_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_token_type CHECK (token_type IN (
        'global', 'digest_specific', 'category_specific'
    ))
);

-- Data processing records for GDPR compliance
CREATE TABLE data_processing_records (
    record_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    processing_type VARCHAR(100) NOT NULL,
    legal_basis VARCHAR(50) NOT NULL,
    purpose TEXT NOT NULL,
    data_categories TEXT[] NOT NULL,
    retention_period INTEGER NOT NULL, -- Days
    third_party_sharing BOOLEAN DEFAULT false,
    third_parties TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_processing_type CHECK (processing_type IN (
        'email_marketing', 'analytics', 'personalization', 'research', 'service_provision'
    )),
    CONSTRAINT chk_legal_basis CHECK (legal_basis IN (
        'consent', 'legitimate_interest', 'contract', 'legal_obligation', 'vital_interests', 'public_task'
    ))
);

-- GDPR request tracking
CREATE TABLE gdpr_requests (
    request_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    requester_email VARCHAR(255) NOT NULL,
    request_type VARCHAR(50) NOT NULL,
    request_details JSONB DEFAULT '{}',
    request_status VARCHAR(50) DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    response_due_date TIMESTAMP NOT NULL,
    processed_at TIMESTAMP,
    completed_at TIMESTAMP,
    response_data JSONB,
    
    CONSTRAINT chk_gdpr_request_type CHECK (request_type IN (
        'access', 'rectification', 'erasure', 'portability', 'restriction', 'objection'
    )),
    CONSTRAINT chk_gdpr_request_status CHECK (request_status IN (
        'pending', 'in_progress', 'completed', 'rejected', 'expired'
    ))
);

-- Email suppression list for bounces and complaints
CREATE TABLE email_suppression_list (
    suppression_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(user_id) ON DELETE SET NULL,
    email VARCHAR(255) NOT NULL,
    reason VARCHAR(100) NOT NULL,
    source VARCHAR(50) DEFAULT 'manual',
    suppression_type VARCHAR(50) DEFAULT 'permanent',
    metadata JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    removed_at TIMESTAMP,
    
    CONSTRAINT chk_suppression_reason CHECK (reason IN (
        'hard_bounce', 'soft_bounce', 'spam_complaint', 'unsubscribe', 'invalid_email', 'manual'
    )),
    CONSTRAINT chk_suppression_type CHECK (suppression_type IN (
        'permanent', 'temporary', 'manual'
    ))
);

-- User preference change history for audit trail
CREATE TABLE user_preference_history (
    history_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    change_type VARCHAR(50) NOT NULL,
    old_values JSONB DEFAULT '{}',
    new_values JSONB DEFAULT '{}',
    source VARCHAR(50) NOT NULL,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_change_type CHECK (change_type IN (
        'subscription', 'unsubscription', 'frequency_change', 'category_change', 'preference_update'
    ))
);

-- Compliance events for audit and reporting
CREATE TABLE compliance_events (
    event_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(user_id) ON DELETE SET NULL,
    event_type VARCHAR(100) NOT NULL,
    event_method VARCHAR(100),
    event_metadata JSONB DEFAULT '{}',
    compliance_requirement VARCHAR(50) NOT NULL,
    processing_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolution_status VARCHAR(50) DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_compliance_requirement CHECK (compliance_requirement IN (
        'CAN_SPAM', 'GDPR', 'CCPA', 'CASL', 'PIPEDA'
    ))
);

-- Email template versions for A/B testing
CREATE TABLE email_template_versions (
    version_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    template_type VARCHAR(100) NOT NULL,
    version_name VARCHAR(100) NOT NULL,
    html_content TEXT NOT NULL,
    text_content TEXT,
    subject_template TEXT NOT NULL,
    is_active BOOLEAN DEFAULT false,
    is_default BOOLEAN DEFAULT false,
    ab_test_weight INTEGER DEFAULT 100, -- Percentage
    performance_metrics JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(template_type, version_name)
);

-- A/B test campaigns for email optimization
CREATE TABLE email_ab_tests (
    test_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    test_name VARCHAR(200) NOT NULL,
    template_type VARCHAR(100) NOT NULL,
    test_variants JSONB NOT NULL, -- Array of variant configurations
    test_status VARCHAR(50) DEFAULT 'draft',
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    target_users_count INTEGER DEFAULT 0,
    success_metric VARCHAR(100) DEFAULT 'open_rate',
    confidence_level DECIMAL(3,2) DEFAULT 0.95,
    results JSONB DEFAULT '{}',
    winner_variant VARCHAR(100),
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_test_status CHECK (test_status IN (
        'draft', 'active', 'paused', 'completed', 'cancelled'
    ))
);

-- Email campaign management
CREATE TABLE email_campaigns (
    campaign_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    campaign_name VARCHAR(200) NOT NULL,
    campaign_type VARCHAR(100) NOT NULL,
    template_type VARCHAR(100) NOT NULL,
    target_criteria JSONB NOT NULL, -- User selection criteria
    schedule_config JSONB DEFAULT '{}',
    campaign_status VARCHAR(50) DEFAULT 'draft',
    sent_count INTEGER DEFAULT 0,
    delivered_count INTEGER DEFAULT 0,
    opened_count INTEGER DEFAULT 0,
    clicked_count INTEGER DEFAULT 0,
    unsubscribed_count INTEGER DEFAULT 0,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_campaign_type CHECK (campaign_type IN (
        'one_time', 'recurring', 'triggered', 'ab_test'
    )),
    CONSTRAINT chk_campaign_status CHECK (campaign_status IN (
        'draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled'
    ))
);

-- ============================================================
-- INDEXES FOR EMAIL SYSTEM TABLES
-- ============================================================

-- Email tracking indexes
CREATE INDEX idx_email_tracking_events_user_id ON email_tracking_events(user_id);
CREATE INDEX idx_email_tracking_events_digest_id ON email_tracking_events(digest_id);
CREATE INDEX idx_email_tracking_events_type_date ON email_tracking_events(event_type, created_at DESC);
CREATE INDEX idx_email_tracking_events_user_type ON email_tracking_events(user_id, event_type);

-- Link tracking indexes
CREATE INDEX idx_email_link_tracking_user_id ON email_link_tracking(user_id);
CREATE INDEX idx_email_link_tracking_digest_id ON email_link_tracking(digest_id);
CREATE INDEX idx_email_link_tracking_url ON email_link_tracking(original_url);
CREATE INDEX idx_email_link_tracking_clicks ON email_link_tracking(click_count DESC);

-- Consent records indexes
CREATE INDEX idx_email_consent_user_id ON email_consent_records(user_id);
CREATE INDEX idx_email_consent_email ON email_consent_records(email);
CREATE INDEX idx_email_consent_status ON email_consent_records(consent_status);
CREATE INDEX idx_email_consent_token ON email_consent_records(confirmation_token);
CREATE INDEX idx_email_consent_created ON email_consent_records(created_at DESC);

-- Unsubscribe tokens indexes
CREATE INDEX idx_unsubscribe_tokens_user_id ON unsubscribe_tokens(user_id);
CREATE INDEX idx_unsubscribe_tokens_email ON unsubscribe_tokens(email);
CREATE INDEX idx_unsubscribe_tokens_expires ON unsubscribe_tokens(expires_at);
CREATE INDEX idx_unsubscribe_tokens_used ON unsubscribe_tokens(is_used, used_at);

-- Data processing indexes
CREATE INDEX idx_data_processing_user_id ON data_processing_records(user_id);
CREATE INDEX idx_data_processing_type ON data_processing_records(processing_type);
CREATE INDEX idx_data_processing_legal_basis ON data_processing_records(legal_basis);
CREATE INDEX idx_data_processing_created ON data_processing_records(created_at DESC);

-- GDPR requests indexes
CREATE INDEX idx_gdpr_requests_user_id ON gdpr_requests(user_id);
CREATE INDEX idx_gdpr_requests_email ON gdpr_requests(requester_email);
CREATE INDEX idx_gdpr_requests_type ON gdpr_requests(request_type);
CREATE INDEX idx_gdpr_requests_status ON gdpr_requests(request_status);
CREATE INDEX idx_gdpr_requests_due_date ON gdpr_requests(response_due_date);

-- Suppression list indexes
CREATE INDEX idx_email_suppression_user_id ON email_suppression_list(user_id);
CREATE INDEX idx_email_suppression_email ON email_suppression_list(email);
CREATE INDEX idx_email_suppression_active ON email_suppression_list(is_active) WHERE is_active = true;
CREATE INDEX idx_email_suppression_reason ON email_suppression_list(reason);

-- Preference history indexes
CREATE INDEX idx_preference_history_user_id ON user_preference_history(user_id);
CREATE INDEX idx_preference_history_type ON user_preference_history(change_type);
CREATE INDEX idx_preference_history_timestamp ON user_preference_history(timestamp DESC);

-- Compliance events indexes
CREATE INDEX idx_compliance_events_user_id ON compliance_events(user_id);
CREATE INDEX idx_compliance_events_type ON compliance_events(event_type);
CREATE INDEX idx_compliance_events_requirement ON compliance_events(compliance_requirement);
CREATE INDEX idx_compliance_events_time ON compliance_events(processing_time DESC);

-- Template versions indexes
CREATE INDEX idx_email_template_versions_type ON email_template_versions(template_type);
CREATE INDEX idx_email_template_versions_active ON email_template_versions(template_type, is_active) WHERE is_active = true;
CREATE INDEX idx_email_template_versions_default ON email_template_versions(template_type, is_default) WHERE is_default = true;

-- A/B test indexes
CREATE INDEX idx_email_ab_tests_template_type ON email_ab_tests(template_type);
CREATE INDEX idx_email_ab_tests_status ON email_ab_tests(test_status);
CREATE INDEX idx_email_ab_tests_dates ON email_ab_tests(start_date, end_date);

-- Campaign indexes
CREATE INDEX idx_email_campaigns_type ON email_campaigns(campaign_type);
CREATE INDEX idx_email_campaigns_status ON email_campaigns(campaign_status);
CREATE INDEX idx_email_campaigns_template ON email_campaigns(template_type);
CREATE INDEX idx_email_campaigns_created ON email_campaigns(created_at DESC);

-- ============================================================
-- TRIGGERS FOR EMAIL SYSTEM TABLES
-- ============================================================

-- Update updated_at columns
CREATE TRIGGER update_email_consent_records_updated_at 
    BEFORE UPDATE ON email_consent_records
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_data_processing_records_updated_at 
    BEFORE UPDATE ON data_processing_records
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_email_template_versions_updated_at 
    BEFORE UPDATE ON email_template_versions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_email_ab_tests_updated_at 
    BEFORE UPDATE ON email_ab_tests
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_email_campaigns_updated_at 
    BEFORE UPDATE ON email_campaigns
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- FUNCTIONS FOR EMAIL SYSTEM
-- ============================================================

-- Function to get user email consent status
CREATE OR REPLACE FUNCTION get_user_email_consent(user_uuid UUID)
RETURNS TABLE (
    has_consent BOOLEAN,
    consent_type VARCHAR(50),
    consent_date TIMESTAMP,
    can_send_email BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        (ecr.consent_status = 'confirmed') as has_consent,
        ecr.consent_type,
        COALESCE(ecr.confirmed_at, ecr.created_at) as consent_date,
        (ecr.consent_status = 'confirmed' AND 
         NOT EXISTS (
            SELECT 1 FROM email_suppression_list esl 
            WHERE esl.user_id = user_uuid AND esl.is_active = true
         )) as can_send_email
    FROM email_consent_records ecr
    WHERE ecr.user_id = user_uuid
    ORDER BY ecr.created_at DESC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- Function to calculate email engagement metrics
CREATE OR REPLACE FUNCTION calculate_email_engagement_metrics(
    start_date DATE DEFAULT CURRENT_DATE - INTERVAL '30 days',
    end_date DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
    metric_name VARCHAR(100),
    metric_value DECIMAL(10,4),
    metric_count INTEGER
) AS $$
BEGIN
    RETURN QUERY
    WITH email_stats AS (
        SELECT 
            COUNT(*) as total_sent,
            COUNT(CASE WHEN edl.delivery_status IN ('delivered', 'opened', 'clicked') THEN 1 END) as delivered,
            COUNT(CASE WHEN edl.delivery_status IN ('opened', 'clicked') THEN 1 END) as opened,
            COUNT(CASE WHEN edl.delivery_status = 'clicked' THEN 1 END) as clicked,
            COUNT(CASE WHEN edl.delivery_status = 'bounced' THEN 1 END) as bounced,
            COUNT(CASE WHEN edl.delivery_status = 'unsubscribed' THEN 1 END) as unsubscribed
        FROM email_delivery_logs edl
        WHERE edl.delivery_timestamp::date BETWEEN start_date AND end_date
    )
    SELECT 'total_emails'::VARCHAR(100), total_sent::DECIMAL(10,4), total_sent FROM email_stats
    UNION ALL
    SELECT 'delivery_rate'::VARCHAR(100), 
           CASE WHEN total_sent > 0 THEN (delivered::DECIMAL / total_sent) * 100 ELSE 0 END,
           delivered FROM email_stats
    UNION ALL
    SELECT 'open_rate'::VARCHAR(100), 
           CASE WHEN delivered > 0 THEN (opened::DECIMAL / delivered) * 100 ELSE 0 END,
           opened FROM email_stats
    UNION ALL
    SELECT 'click_rate'::VARCHAR(100), 
           CASE WHEN opened > 0 THEN (clicked::DECIMAL / opened) * 100 ELSE 0 END,
           clicked FROM email_stats
    UNION ALL
    SELECT 'bounce_rate'::VARCHAR(100), 
           CASE WHEN total_sent > 0 THEN (bounced::DECIMAL / total_sent) * 100 ELSE 0 END,
           bounced FROM email_stats
    UNION ALL
    SELECT 'unsubscribe_rate'::VARCHAR(100), 
           CASE WHEN delivered > 0 THEN (unsubscribed::DECIMAL / delivered) * 100 ELSE 0 END,
           unsubscribed FROM email_stats;
END;
$$ LANGUAGE plpgsql;

-- Function to clean up expired tokens and old data
CREATE OR REPLACE FUNCTION cleanup_email_system_data()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER := 0;
    temp_count INTEGER;
BEGIN
    -- Delete expired unsubscribe tokens
    DELETE FROM unsubscribe_tokens 
    WHERE expires_at < CURRENT_TIMESTAMP AND is_used = false;
    GET DIAGNOSTICS temp_count = ROW_COUNT;
    deleted_count := deleted_count + temp_count;

    -- Delete old email tracking events (older than 2 years)
    DELETE FROM email_tracking_events 
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '2 years';
    GET DIAGNOSTICS temp_count = ROW_COUNT;
    deleted_count := deleted_count + temp_count;

    -- Delete old email delivery logs (older than 2 years)
    DELETE FROM email_delivery_logs 
    WHERE delivery_timestamp < CURRENT_TIMESTAMP - INTERVAL '2 years';
    GET DIAGNOSTICS temp_count = ROW_COUNT;
    deleted_count := deleted_count + temp_count;

    -- Delete old compliance events (older than 7 years for legal compliance)
    DELETE FROM compliance_events 
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '7 years';
    GET DIAGNOSTICS temp_count = ROW_COUNT;
    deleted_count := deleted_count + temp_count;

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- COMMENTS FOR DOCUMENTATION
-- ============================================================

COMMENT ON TABLE email_tracking_events IS 'Tracks all email interaction events for analytics and compliance';
COMMENT ON TABLE email_link_tracking IS 'Tracks clicks on specific links within emails';
COMMENT ON TABLE email_consent_records IS 'GDPR and CAN-SPAM compliant consent tracking for email communications';
COMMENT ON TABLE unsubscribe_tokens IS 'Secure tokens for unsubscribe links with expiration';
COMMENT ON TABLE data_processing_records IS 'GDPR Article 30 compliant record of processing activities';
COMMENT ON TABLE gdpr_requests IS 'Tracks GDPR data subject access requests and their processing';
COMMENT ON TABLE email_suppression_list IS 'Global suppression list for bounced emails and spam complaints';
COMMENT ON TABLE user_preference_history IS 'Audit trail of user email preference changes';
COMMENT ON TABLE compliance_events IS 'Audit log for compliance-related events and actions';
COMMENT ON TABLE email_template_versions IS 'Version control for email templates with A/B testing support';
COMMENT ON TABLE email_ab_tests IS 'A/B test configuration and results for email optimization';
COMMENT ON TABLE email_campaigns IS 'Email campaign management and tracking';

-- ============================================================
-- END OF EMAIL SYSTEM SCHEMA
-- ============================================================